package com.datatraffic.databasecontrol;

import java.util.ArrayList;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.datatraffic.salesup.model.Form;

import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.content.pm.FeatureInfo;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.preference.PreferenceManager;
import android.util.Log;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseControl {

	private SharedPreferences prefs;
	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;
	private static final String KEY_ROWID = "_id", 
			KEY_LAT = "latitude", 
			KEY_LONG = "longitude",
			KEY_PERCISION = "percision", 
			KEY_HEADING = "heading",
			KEY_SPEED = "speed",
			TABLE_USERS = "Users", 
			TABLE_FORMS = "Forms",
			KEY_NOMBRE = "nombre", 
			KEY_APELLIDO = "apellido",
			KEY_TIPO_DOC = "tipo_documento", 
			KEY_DOC = "documento",
			KEY_TELE_1 = "numero_telefonico_1",
			KEY_TELE_2 = "numero_telefonico_2",
			KEY_CELL_1 = "numero_celular_1", 
			KEY_CELL_2 = "numero_celular_2",
			KEY_OPERATOR = "operador_actual_de_television",
			KEY_COBERTURA  = "prospecto_ubicado_en_zona_cobertura_NET",
			KEY_PRODUCTO_INTERES = "producto_interes",
			KEY_TIME_DATE = "fecha_y_hora_proximo_contacto",
			KEY_TIPO_CONTACTO = "tipo_de_contacto_a_realizar",
			KEY_MENSAJE_PROXIMO_CONTACTO = "mensaje_proximo_contacto",
			KEY_OBSERVACIONES = "observaciones", 
			KEY_ESTADO = "estado",
			KEY_FECHA_REGISTRO = "fecha_de_registro",
			KEY_LAST_VER_ID = "last_version_id",
			KEY_SENT = "sent",
			KEY_SERVER_ID = "server_id",
			KEY_USER = "user",
			KEY_PASS = "password",
			KEY_ACTUAL = "actual",
			KEY_EMAIL = "email",
			KEY_DIRECCION = "direccion",
			KEY_AGREE = "acepta";
	private static final String KEY_SIGN = "firma";

	public DatabaseControl(Context context) {
		this.setContext(context);
		dbHelper = new DBHelper(context);
		prefs = PreferenceManager.getDefaultSharedPreferences(context);
	}

	public void close() {
		dbHelper.close();
	}

	public DatabaseControl open() throws SQLiteException {
		database = dbHelper.getWritableDatabase();
		return this;
	}

	public JSONObject addNewForm(JSONObject json) {
		
		JSONObject resultado = jsonError();
		
		try
		{
			ContentValues valuesInsert = new ContentValues();
			
			valuesInsert.put(KEY_NOMBRE, json.optString("names"));
			valuesInsert.put(KEY_APELLIDO, json.optString("surnames"));
			valuesInsert.put(KEY_TIPO_DOC, json.optString("type_id"));
			valuesInsert.put(KEY_DOC, json.optString("prospect_id"));
			valuesInsert.put(KEY_TELE_1, json.optString("telephone1"));
			valuesInsert.put(KEY_TELE_2, json.optString("telephone2"));
			valuesInsert.put(KEY_CELL_1, json.optString("celular1"));
			valuesInsert.put(KEY_CELL_2, json.optString("celular2"));
			valuesInsert.put(KEY_OPERATOR, json.optString("operator"));

			valuesInsert.put(KEY_COBERTURA, json.optString("coverage"));
			valuesInsert.put(KEY_PRODUCTO_INTERES,json.optString("product"));
			valuesInsert.put(KEY_TIME_DATE, json.optString("next_contact"));
			valuesInsert.put(KEY_TIPO_CONTACTO, json.optString("type_contact"));
			valuesInsert.put(KEY_MENSAJE_PROXIMO_CONTACTO, json.optString("msg_contact"));
			valuesInsert.put(KEY_OBSERVACIONES, json.optString("observations"));
			valuesInsert.put(KEY_ESTADO, json.optString("status"));
			valuesInsert.put(KEY_EMAIL, json.optString("email"));
			valuesInsert.put(KEY_DIRECCION, json.optString("address"));
			
			valuesInsert.put(KEY_USER, json.optInt("user"));
			
			valuesInsert.put(KEY_ACTUAL, true);
			
			valuesInsert.put(KEY_FECHA_REGISTRO, json.optString("date_time"));
			valuesInsert.put(KEY_LAT, json.optString("latitude"));
			valuesInsert.put(KEY_LONG, json.optString("longitude"));
			valuesInsert.put(KEY_SPEED, json.optString("speed"));
			valuesInsert.put(KEY_HEADING, json.optString("heading"));
			valuesInsert.put(KEY_PERCISION, json.optString("precision"));
			
			valuesInsert.put(KEY_LAST_VER_ID, json.optInt("last_version_id"));
			valuesInsert.put(KEY_SERVER_ID, json.optInt("server_id"));
			valuesInsert.put(KEY_SENT, json.optInt("sent"));
			
			
			try
			{
				valuesInsert.put(KEY_SIGN, json.optString("sign"));
			}
			catch(Exception e)
			{
				
			}
			
			Log.i("NUEVO AGREE",json.optString("agree"));
			
			try
			{
				valuesInsert.put(KEY_AGREE, json.optString("agree"));
			}
			catch(Exception e)
			{
				e.printStackTrace();
				valuesInsert.put(KEY_AGREE, "false");
			}			
			
			Log.i("BD: ","INSERTAR");
			
			int local_id = (int) database.insert(TABLE_FORMS, null, valuesInsert);		
			
			Log.i("LOCAL ID: ","local_id");
			
			if(local_id > 0)
			{
				resultado.put("error", "false");
				resultado.put("msg", "OK");
				resultado.put("total", "1");
				
				json.put("local_id", local_id);	
				
				JSONArray array = new JSONArray();
				array.put(json);
				
				resultado.put("data", array);
			}
			
			try
			{
				ContentValues valuesUpdate = new ContentValues();
				valuesUpdate.put(KEY_ACTUAL, false);
				database.update(TABLE_FORMS, valuesUpdate, KEY_ROWID + "=" + json.optInt("last_version_id"), null);
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return resultado; 
	}

	public JSONObject getUnsentForms() {
		
		JSONObject resultado = jsonError();
		
		try {
			Cursor dbCursor = database.rawQuery("SELECT * FROM " + TABLE_FORMS + " WHERE sent = 0 ORDER BY last_version_id, " + KEY_ROWID, null);
			if (dbCursor.moveToFirst())
			{
				int contador = 0;
				JSONArray array = new JSONArray();
				for (dbCursor.moveToFirst(); !dbCursor.isAfterLast(); dbCursor.moveToNext()) {
					JSONObject aux = new JSONObject();
									
					aux.put("local_id", dbCursor.getInt(dbCursor.getColumnIndex(KEY_ROWID)));
					aux.put("names", dbCursor.getString(dbCursor.getColumnIndex(KEY_NOMBRE)));
					aux.put("surnames", dbCursor.getString(dbCursor.getColumnIndex(KEY_APELLIDO)));
					aux.put("type_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_DOC)));
					aux.put("prospect_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_DOC)));
					aux.put("telephone1", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_1)));
					aux.put("telephone2", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_2)));
					aux.put("celular1", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_1)));
					aux.put("celular2", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_2)));
					aux.put("operator", dbCursor.getString(dbCursor.getColumnIndex(KEY_OPERATOR)));
					aux.put("coverage", dbCursor.getString(dbCursor.getColumnIndex(KEY_COBERTURA)));
					aux.put("product", dbCursor.getString(dbCursor.getColumnIndex(KEY_PRODUCTO_INTERES)));					
					aux.put("next_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIME_DATE)));
					aux.put("type_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_CONTACTO)));
					aux.put("msg_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_MENSAJE_PROXIMO_CONTACTO)));
					aux.put("observations", dbCursor.getString(dbCursor.getColumnIndex(KEY_OBSERVACIONES)));
					aux.put("email", dbCursor.getString(dbCursor.getColumnIndex(KEY_EMAIL)));
					aux.put("address", dbCursor.getString(dbCursor.getColumnIndex(KEY_DIRECCION)));
					
					aux.put("status", dbCursor.getString(dbCursor.getColumnIndex(KEY_ESTADO)));
					aux.put("user", dbCursor.getInt(dbCursor.getColumnIndex(KEY_USER)));
					aux.put("date_time", dbCursor.getString(dbCursor.getColumnIndex(KEY_FECHA_REGISTRO)));
					aux.put("latitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LAT)));
					aux.put("longitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LONG)));
					aux.put("speed", dbCursor.getInt(dbCursor.getColumnIndex(KEY_SPEED)));
					aux.put("heading", dbCursor.getInt(dbCursor.getColumnIndex(KEY_HEADING)));
					aux.put("precision", dbCursor.getInt(dbCursor.getColumnIndex(KEY_PERCISION)));				
					
					aux.put("last_version_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_LAST_VER_ID)));
					aux.put("server_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SERVER_ID)));
					aux.put("sent", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SENT)));
					
					aux.put("sign", dbCursor.getString(dbCursor.getColumnIndex(KEY_SIGN)));
					
					aux.put("agree", dbCursor.getString(dbCursor.getColumnIndex(KEY_AGREE)));
					
					array.put(aux);
					
					contador++;
				}
				
				resultado.put("error", "false");
				resultado.put("msg", "OK");
				resultado.put("total", contador);				
				resultado.put("data", array);				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultado;
	}
	
	public JSONObject getFormsByQuery(String query, int start, int limit, int user, String filtro) throws JSONException {
		Cursor dbCursor;
		JSONObject resultado = jsonError();
		try {
			String from = " From " + TABLE_FORMS;
			String where;
			if(filtro.equals(""))
			{
				where = " WHERE actual AND user = " + user ;

			}
			else
				where = " WHERE actual AND user = " + user + " AND " + KEY_ESTADO + " = '" +filtro+ "'";
			
			if(query.length() > 0)
			{
				String[] palabras_array = query.trim().split("( )+");
				if(palabras_array.length > 0)
				{
					String palabra = palabras_array[0];
					
					where += " AND ( UPPER(" + KEY_DOC + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_NOMBRE + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_APELLIDO + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_TELE_1 + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_TELE_2 + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_CELL_1 + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_CELL_2 + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_EMAIL + ") LIKE '%" + palabra + "%'";
					where += " OR UPPER(" + KEY_ESTADO + ") LIKE '%" + filtro + "%'";
					
					for (int i = 1; i < palabras_array.length; i++) {
						palabra = palabras_array[i];
						where += " OR  UPPER(" + KEY_DOC + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_NOMBRE + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_APELLIDO + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_TELE_1 + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_TELE_2 + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_CELL_1 + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_CELL_2 + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_EMAIL + ") LIKE '%" + palabra + "%'";
						where += " OR UPPER(" + KEY_ESTADO + ") LIKE '%" + filtro + "%'";
						
					}
					
					where += " )";
				}
			}
			
			String complemento = " ORDER BY "+  KEY_ROWID + " DESC";
			complemento += " LIMIT " + limit + " OFFSET " + (start*limit);
			
			String sql = "SELECT * "+from+where+complemento;
			
			Log.i ("SQL ", sql);	
			
			dbCursor = database.rawQuery(sql, null);
			
			JSONArray array = new JSONArray();
			
			if (dbCursor.moveToFirst())
			{
				for (dbCursor.moveToFirst(); !dbCursor.isAfterLast(); dbCursor.moveToNext()) {
					JSONObject aux = new JSONObject();
					

					aux.put("local_id", dbCursor.getInt(dbCursor.getColumnIndex(KEY_ROWID)));
					aux.put("names", dbCursor.getString(dbCursor.getColumnIndex(KEY_NOMBRE)));
					aux.put("surnames", dbCursor.getString(dbCursor.getColumnIndex(KEY_APELLIDO)));
					aux.put("type_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_DOC)));
					aux.put("prospect_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_DOC)));
					aux.put("telephone1", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_1)));
					aux.put("telephone2", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_2)));
					aux.put("celular1", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_1)));
					aux.put("celular2", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_2)));
					aux.put("operator", dbCursor.getString(dbCursor.getColumnIndex(KEY_OPERATOR)));
					aux.put("cobertura", dbCursor.getString(dbCursor.getColumnIndex(KEY_COBERTURA)));
					aux.put("producto_interes", dbCursor.getString(dbCursor.getColumnIndex(KEY_PRODUCTO_INTERES)));	
					aux.put("date_time", dbCursor.getString(dbCursor.getColumnIndex(KEY_FECHA_REGISTRO)));
					aux.put("next_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIME_DATE)));
					aux.put("type_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_CONTACTO)));
					aux.put("msg_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_MENSAJE_PROXIMO_CONTACTO)));
					aux.put("observations", dbCursor.getString(dbCursor.getColumnIndex(KEY_OBSERVACIONES)));
					aux.put("status", dbCursor.getString(dbCursor.getColumnIndex(KEY_ESTADO)));
					aux.put("email", dbCursor.getString(dbCursor.getColumnIndex(KEY_EMAIL)));
					aux.put("direccion", dbCursor.getString(dbCursor.getColumnIndex(KEY_DIRECCION)));
					
					
					aux.put("user", dbCursor.getInt(dbCursor.getColumnIndex(KEY_USER)));
					aux.put("date_time", dbCursor.getString(dbCursor.getColumnIndex(KEY_FECHA_REGISTRO)));
					aux.put("latitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LAT)));
					aux.put("longitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LONG)));
					aux.put("speed", dbCursor.getInt(dbCursor.getColumnIndex(KEY_SPEED)));
					aux.put("heading", dbCursor.getInt(dbCursor.getColumnIndex(KEY_HEADING)));
					aux.put("precision", dbCursor.getInt(dbCursor.getColumnIndex(KEY_PERCISION)));				
					
					aux.put("last_version_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_LAST_VER_ID)));
					aux.put("server_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SERVER_ID)));
					aux.put("sent", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SENT)));
					
					aux.put("agree", dbCursor.getString(dbCursor.getColumnIndex(KEY_AGREE)));
					
					array.put(aux);
	
				}
			}
			resultado.put("data", array);
			
			dbCursor.close();
			String sqlTotal = "SELECT count(*) AS total "+from+where;
			dbCursor = database.rawQuery(sqlTotal, null);
			int total = 100;
			for (dbCursor.moveToFirst(); !dbCursor.isAfterLast(); dbCursor.moveToNext()) {
				total = dbCursor.getInt(dbCursor.getColumnIndex("total"));
			}
			
			resultado.put("error", "false");
			resultado.put("msg", "OK");
			resultado.put("total", total);			
		} catch (SQLiteException e) {
			e.printStackTrace();
		}
		
		return resultado;
	}	
	
	public JSONObject getFormById(int form_id) {
		Cursor dbCursor;
		JSONObject resultado = jsonError();
		
		try {
			dbCursor = database.rawQuery("SELECT * FROM " + TABLE_FORMS + " WHERE "+KEY_ROWID+" = " + form_id, null);
			if (dbCursor.moveToFirst())
			{
				try
				{
					int contador = 0;
					JSONArray array = new JSONArray();					
					for (dbCursor.moveToFirst(); !dbCursor.isAfterLast(); dbCursor.moveToNext()) {
						
						JSONObject aux = new JSONObject();
						
						aux.put("local_id", dbCursor.getInt(dbCursor.getColumnIndex(KEY_ROWID)));
						aux.put("names", dbCursor.getString(dbCursor.getColumnIndex(KEY_NOMBRE)));
						aux.put("surnames", dbCursor.getString(dbCursor.getColumnIndex(KEY_APELLIDO)));
						aux.put("type_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_DOC)));
						aux.put("prospect_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_DOC)));
						aux.put("telephone1", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_1)));
						aux.put("telephone2", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_2)));
						aux.put("celular1", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_1)));
						aux.put("celular2", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_2)));
						aux.put("operator", dbCursor.getString(dbCursor.getColumnIndex(KEY_OPERATOR)));
						aux.put("cobertura", dbCursor.getString(dbCursor.getColumnIndex(KEY_COBERTURA)));
						aux.put("producto_interes", dbCursor.getString(dbCursor.getColumnIndex(KEY_PRODUCTO_INTERES)));						
						aux.put("next_contact", dbCursor.getLong(dbCursor.getColumnIndex(KEY_TIME_DATE)));
						aux.put("date_time", dbCursor.getString(dbCursor.getColumnIndex(KEY_FECHA_REGISTRO)));
						aux.put("type_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_CONTACTO)));
						aux.put("msg_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_MENSAJE_PROXIMO_CONTACTO)));
						aux.put("observations", dbCursor.getString(dbCursor.getColumnIndex(KEY_OBSERVACIONES)));
						aux.put("email", dbCursor.getString(dbCursor.getColumnIndex(KEY_EMAIL)));
						aux.put("address", dbCursor.getString(dbCursor.getColumnIndex(KEY_DIRECCION)));						
						
						aux.put("status", dbCursor.getString(dbCursor.getColumnIndex(KEY_ESTADO)));
						aux.put("user", dbCursor.getInt(dbCursor.getColumnIndex(KEY_USER)));
						aux.put("latitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LAT)));
						aux.put("longitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LONG)));
						aux.put("speed", dbCursor.getInt(dbCursor.getColumnIndex(KEY_SPEED)));
						aux.put("heading", dbCursor.getInt(dbCursor.getColumnIndex(KEY_HEADING)));
						aux.put("precision", dbCursor.getInt(dbCursor.getColumnIndex(KEY_PERCISION)));
						
						aux.put("last_version_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_LAST_VER_ID)));
						aux.put("server_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SERVER_ID)));
						aux.put("sent", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SENT)));
						
						aux.put("agree", dbCursor.getString(dbCursor.getColumnIndex(KEY_AGREE)));
						
						array.put(aux);
						
						contador++;
					}
					
					resultado.put("error", "false");
					resultado.put("msg", "OK");
					resultado.put("total", contador);				
					resultado.put("data", array);
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultado;
	}
	
	public JSONObject getFormByTipoCliente(String tipo_cliente, int user) {
		Cursor dbCursor;
		
		JSONObject resultado = jsonError();
		
		try {
			dbCursor = database.rawQuery("SELECT * FROM " + TABLE_FORMS + " WHERE "+KEY_ESTADO+" = '"+ tipo_cliente+ "' and " +KEY_USER+" = '" +user+ "'", null);
			
			if (dbCursor.moveToFirst())
			{
				try
				{
					int contador = 0;
					JSONArray array = new JSONArray();					
					for (dbCursor.moveToFirst(); !dbCursor.isAfterLast(); dbCursor.moveToNext()) {
						
						JSONObject aux = new JSONObject();
						
						aux.put("local_id", dbCursor.getInt(dbCursor.getColumnIndex(KEY_ROWID)));
						aux.put("names", dbCursor.getString(dbCursor.getColumnIndex(KEY_NOMBRE)));
						aux.put("surnames", dbCursor.getString(dbCursor.getColumnIndex(KEY_APELLIDO)));
						aux.put("type_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_DOC)));
						aux.put("prospect_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_DOC)));
						aux.put("telephone1", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_1)));
						aux.put("telephone2", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_2)));
						aux.put("celular1", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_1)));
						aux.put("celular2", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_2)));
						aux.put("operator", dbCursor.getString(dbCursor.getColumnIndex(KEY_OPERATOR)));
						aux.put("cobertura", dbCursor.getString(dbCursor.getColumnIndex(KEY_COBERTURA)));
						aux.put("producto_interes", dbCursor.getString(dbCursor.getColumnIndex(KEY_PRODUCTO_INTERES)));						
						aux.put("next_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIME_DATE)));
						
						aux.put("type_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_CONTACTO)));
						aux.put("msg_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_MENSAJE_PROXIMO_CONTACTO)));
						aux.put("observations", dbCursor.getString(dbCursor.getColumnIndex(KEY_OBSERVACIONES)));
						aux.put("email", dbCursor.getString(dbCursor.getColumnIndex(KEY_EMAIL)));
						aux.put("address", dbCursor.getString(dbCursor.getColumnIndex(KEY_DIRECCION)));
						
						
						aux.put("status", dbCursor.getString(dbCursor.getColumnIndex(KEY_ESTADO)));
						aux.put("user", dbCursor.getInt(dbCursor.getColumnIndex(KEY_USER)));
						aux.put("date_time", dbCursor.getString(dbCursor.getColumnIndex(KEY_FECHA_REGISTRO)));
						aux.put("latitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LAT)));
						aux.put("longitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LONG)));
						aux.put("speed", dbCursor.getInt(dbCursor.getColumnIndex(KEY_SPEED)));
						aux.put("heading", dbCursor.getInt(dbCursor.getColumnIndex(KEY_HEADING)));
						aux.put("precision", dbCursor.getInt(dbCursor.getColumnIndex(KEY_PERCISION)));
						
						aux.put("last_version_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_LAST_VER_ID)));
						aux.put("server_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SERVER_ID)));
						aux.put("sent", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SENT)));
						
						aux.put("agree", dbCursor.getString(dbCursor.getColumnIndex(KEY_AGREE)));
						
						array.put(aux);
						
						contador++;
					}
					
					resultado.put("error", "false");
					resultado.put("msg", "OK");
					resultado.put("total", contador);				
					resultado.put("data", array);
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultado;
	}
		
	public JSONObject getFormByFecha(String fecha,int user) {
		Cursor dbCursor;
	
		
		JSONObject resultado = jsonError();
		
		try {
			dbCursor = database.rawQuery("SELECT * FROM " + TABLE_FORMS + " WHERE "+KEY_FECHA_REGISTRO+" like '%"+fecha+"%' and " +KEY_USER+" = '" +user+ "'", null);
			if (dbCursor.moveToFirst())
			{
				try
				{
					int contador = 0;
					JSONArray array = new JSONArray();					
					for (dbCursor.moveToFirst(); !dbCursor.isAfterLast(); dbCursor.moveToNext()) {
						
						JSONObject aux = new JSONObject();
						
						aux.put("local_id", dbCursor.getInt(dbCursor.getColumnIndex(KEY_ROWID)));
						aux.put("names", dbCursor.getString(dbCursor.getColumnIndex(KEY_NOMBRE)));
						aux.put("surnames", dbCursor.getString(dbCursor.getColumnIndex(KEY_APELLIDO)));
						aux.put("type_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_DOC)));
						aux.put("prospect_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_DOC)));
						aux.put("telephone1", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_1)));
						aux.put("telephone2", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_2)));
						aux.put("celular1", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_1)));
						aux.put("celular2", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_2)));
						aux.put("operator", dbCursor.getString(dbCursor.getColumnIndex(KEY_OPERATOR)));
						aux.put("cobertura", dbCursor.getString(dbCursor.getColumnIndex(KEY_COBERTURA)));
						aux.put("producto_interes", dbCursor.getString(dbCursor.getColumnIndex(KEY_PRODUCTO_INTERES)));						
						aux.put("next_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIME_DATE)));
						aux.put("type_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_CONTACTO)));
						aux.put("msg_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_MENSAJE_PROXIMO_CONTACTO)));
						aux.put("observations", dbCursor.getString(dbCursor.getColumnIndex(KEY_OBSERVACIONES)));
						aux.put("email", dbCursor.getString(dbCursor.getColumnIndex(KEY_EMAIL)));
						aux.put("address", dbCursor.getString(dbCursor.getColumnIndex(KEY_DIRECCION)));
						
						
						aux.put("status", dbCursor.getString(dbCursor.getColumnIndex(KEY_ESTADO)));
						aux.put("user", dbCursor.getInt(dbCursor.getColumnIndex(KEY_USER)));
						aux.put("date_time", dbCursor.getString(dbCursor.getColumnIndex(KEY_FECHA_REGISTRO)));
						aux.put("latitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LAT)));
						aux.put("longitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LONG)));
						aux.put("speed", dbCursor.getInt(dbCursor.getColumnIndex(KEY_SPEED)));
						aux.put("heading", dbCursor.getInt(dbCursor.getColumnIndex(KEY_HEADING)));
						aux.put("precision", dbCursor.getInt(dbCursor.getColumnIndex(KEY_PERCISION)));
						
						aux.put("last_version_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_LAST_VER_ID)));
						aux.put("server_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SERVER_ID)));
						aux.put("sent", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SENT)));
						
						aux.put("agree", dbCursor.getString(dbCursor.getColumnIndex(KEY_AGREE)));
						
						array.put(aux);
						
						contador++;
					}
					
					resultado.put("error", "false");
					resultado.put("msg", "OK");
					resultado.put("total", contador);				
					resultado.put("data", array);
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultado;
	}
	
	public JSONObject getformnombre(String nombre,int user) {
		Cursor dbCursor;	
		
		JSONObject resultado = jsonError();
		
		try {
			dbCursor = database.rawQuery("SELECT * FROM " + TABLE_FORMS + " WHERE "+KEY_NOMBRE+" like '%"+nombre+"%' and " +KEY_USER+" = '" +user+ "'", null);
			if (dbCursor.moveToFirst())
			{
				try
				{
					int contador = 0;
					JSONArray array = new JSONArray();					
					for (dbCursor.moveToFirst(); !dbCursor.isAfterLast(); dbCursor.moveToNext()) {
						
						JSONObject aux = new JSONObject();
						
						aux.put("local_id", dbCursor.getInt(dbCursor.getColumnIndex(KEY_ROWID)));
						aux.put("names", dbCursor.getString(dbCursor.getColumnIndex(KEY_NOMBRE)));
						aux.put("surnames", dbCursor.getString(dbCursor.getColumnIndex(KEY_APELLIDO)));
						aux.put("type_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_DOC)));
						aux.put("prospect_id", dbCursor.getString(dbCursor.getColumnIndex(KEY_DOC)));
						aux.put("telephone1", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_1)));
						aux.put("telephone2", dbCursor.getString(dbCursor.getColumnIndex(KEY_TELE_2)));
						aux.put("celular1", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_1)));
						aux.put("celular2", dbCursor.getString(dbCursor.getColumnIndex(KEY_CELL_2)));
						aux.put("operator", dbCursor.getString(dbCursor.getColumnIndex(KEY_OPERATOR)));
						aux.put("cobertura", dbCursor.getString(dbCursor.getColumnIndex(KEY_COBERTURA)));
						aux.put("producto_interes", dbCursor.getString(dbCursor.getColumnIndex(KEY_PRODUCTO_INTERES)));						
						aux.put("next_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIME_DATE)));
						aux.put("type_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_TIPO_CONTACTO)));
						aux.put("msg_contact", dbCursor.getString(dbCursor.getColumnIndex(KEY_MENSAJE_PROXIMO_CONTACTO)));
						aux.put("observations", dbCursor.getString(dbCursor.getColumnIndex(KEY_OBSERVACIONES)));
						aux.put("email", dbCursor.getString(dbCursor.getColumnIndex(KEY_EMAIL)));
						aux.put("address", dbCursor.getString(dbCursor.getColumnIndex(KEY_DIRECCION)));
						
						
						aux.put("status", dbCursor.getString(dbCursor.getColumnIndex(KEY_ESTADO)));
						aux.put("user", dbCursor.getInt(dbCursor.getColumnIndex(KEY_USER)));
						aux.put("date_time", dbCursor.getString(dbCursor.getColumnIndex(KEY_FECHA_REGISTRO)));
						aux.put("latitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LAT)));
						aux.put("longitude", dbCursor.getDouble(dbCursor.getColumnIndex(KEY_LONG)));
						aux.put("speed", dbCursor.getInt(dbCursor.getColumnIndex(KEY_SPEED)));
						aux.put("heading", dbCursor.getInt(dbCursor.getColumnIndex(KEY_HEADING)));
						aux.put("precision", dbCursor.getInt(dbCursor.getColumnIndex(KEY_PERCISION)));
						
						aux.put("last_version_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_LAST_VER_ID)));
						aux.put("server_id", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SERVER_ID)));
						aux.put("sent", dbCursor.getLong(dbCursor.getColumnIndex(KEY_SENT)));
						
						aux.put("agree", dbCursor.getString(dbCursor.getColumnIndex(KEY_AGREE)));
						
						array.put(aux);
						
						contador++;
					}
					
					resultado.put("error", "false");
					resultado.put("msg", "OK");
					resultado.put("total", contador);				
					resultado.put("data", array);
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultado;
	}
	
	public void updateServerId(int form_id, int server_id) {
		try
		{
			ContentValues values = new ContentValues();
			values.put(KEY_SERVER_ID, server_id);
			values.put(KEY_SENT, 1);
			database.update(TABLE_FORMS, values, KEY_ROWID + "=" + form_id, null);
		} catch (Exception e) {
			e.printStackTrace();
		}			
	}

	public void deleteFormById(int form_id) {
		try
		{
			database.delete(TABLE_FORMS, KEY_ROWID + "=" + form_id, null);
		} catch (Exception e) {
			e.printStackTrace();
		}			
	}
	

	public JSONObject hacerlogin(JSONObject login)
	{
		Log.i("LOGIN", login.toString());
		String user = login.optString("user");
		Log.i("user", user);
		String password = login.optString("password");
		Log.i("password", password);
		
		JSONObject resultado = jsonError();
		try
		{
			Cursor dbCursor = database.rawQuery("Select * From " + TABLE_USERS
					+ " Where user = '" + user + "' and password='" + login.getString("password") + "'", null);
			dbCursor.moveToFirst();
			if (dbCursor.getCount() > 0) {
				
				int server_id = dbCursor.getInt(dbCursor.getColumnIndex(KEY_SERVER_ID));
				
				Log.i("server_id", ""+server_id);
				
				resultado.put("error", "false");
				resultado.put("msg", "OK");
				resultado.put("total", 1);
				
				JSONArray array = new JSONArray();
				JSONObject item = new JSONObject();
				item.put("id",server_id);
				array.put(item);
				
				resultado.put("data", array);			
			}
			else
			{
				resultado.put("msg", "Login o password no validos");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return resultado;
	}

	public boolean guardarlogin(JSONObject login)
	{
		Log.i("LOGIN", login.toString());
		String user = login.optString("user");
		Log.i("user", user);
		String password = login.optString("password");
		Log.i("password", password);
		int server_id = login.optInt("server_id");
		Log.i("server_id", ""+server_id);
		
		boolean resultado = false;
		try
		{		
			Cursor dbCursor = database.rawQuery("Select * From " + TABLE_USERS
					+ " Where " + KEY_USER + " = '" + user + "'", null);
			dbCursor.moveToFirst();
			
			if (dbCursor.getCount() > 0) {
				
				Log.i("LOGIN","YA EXISTIA ");
				
				ContentValues values = new ContentValues();
				values.put(KEY_PASS, password);
				values.put(KEY_SERVER_ID, server_id);
				database.update(TABLE_USERS, values, KEY_USER + "= '" + user + "'", null);	
				
				resultado =  true;
			}
			else
			{
				ContentValues values = new ContentValues();
				
				values.put(KEY_PASS, password);
				values.put(KEY_USER, user);
				values.put(KEY_SERVER_ID, server_id);
				
				int local_id = (int) database.insert(TABLE_USERS, null, values);
				if(local_id>0)
				{
					resultado =  true;
				}
				else
				{
					resultado =  false;
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}			
		return resultado;
	}
	
	public int getNumForms(JSONObject datos)
	{
		try
		{
			Cursor dbCursor = database.rawQuery("Select count(*) as count From " + TABLE_FORMS
					+ " Where user = " + datos.optInt("user") + " and sent=0", null);
			dbCursor.moveToFirst();
			
			if (dbCursor.getCount() > 0) {
				
				int num=dbCursor.getInt(dbCursor.getColumnIndex("count"));
				
				return num;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
	
	public Context getContext() {
		return context;
	}

	public void setContext(Context context) {
		this.context = context;
	}
	
	private JSONObject jsonError()
	{
		JSONObject resultado = new JSONObject();
		try {
			resultado.put("error", "true");
			resultado.put("msg", "Ocurrio un error");
			resultado.put("total", 0);
			resultado.put("data",  new JSONArray());
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultado;
	}	
}